/***********************************************************************************************************************************
 * Name:	Security_DBRole-Owner.sql
 * Author:	Frank Figearo (frank@sqlnerd.me)
 * Summary:
 *	1: Reports on schemas/roles not owned by dbo.
 *	2: Generates T-SQL to change role owner to dbo.
 */
SET IMPLICIT_TRANSACTIONS OFF; WHILE 1 < @@TRANCOUNT ROLLBACK;
GO
SELECT p1.name, p2.type_desc, p2.name
  FROM sys.database_principals p1
	INNER JOIN sys.database_principals p2 ON p1.owning_principal_id = p2.principal_id
  WHERE ISNULL(p1.owning_principal_id,1) <> 1
  ORDER BY p1.name;
GO
SELECT 'ALTER AUTHORIZATION ON ROLE::' + QUOTENAME(p1.name) + ' TO [dbo];	-- Current Owner: ' + QUOTENAME(p2.name)
  FROM sys.database_principals p1
	INNER JOIN sys.database_principals p2 ON p1.owning_principal_id = p2.principal_id
  WHERE ISNULL(p1.owning_principal_id,1) <> 1
  ORDER BY p1.name;
GO